package com.emuporium.gymrat.server.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

public class ExerciseDB {
	public static int deleteEquipment(ArrayList<Integer> delete) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		int result = 0;

		String query = "delete from equipment\r\n" + "where _id = ?";

		try {
			for (Integer i : delete) {

				ps = connection.prepareStatement(query);
				ps.setInt(1, i);

				result += ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static int deleteExercises(ArrayList<Integer> delete) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		int result = 0;

		String query = "delete from exercises\r\n" + "where _id = ?";

		try {
			for (Integer i : delete) {

				ps = connection.prepareStatement(query);
				ps.setInt(1, i);

				result += ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static int deleteVariations(ArrayList<Integer> delete) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		int result = 0;

		String query = "delete from variations\r\n" + "where _id = ?";

		try {
			for (Integer i : delete) {

				ps = connection.prepareStatement(query);
				ps.setInt(1, i);

				result += ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static int insertEquipment(String name, String shortName, int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "insert into equipment (equipment, shortname, creatorid) "
				+ "values (?,?,?)";

		try {

			ps = connection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, shortName);
			if (userId > 0)
				ps.setInt(3, userId); // insert under this users id
			else
				ps.setNull(3, java.sql.Types.INTEGER);

			return ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}

	public static int insertExercise(String name, int muscleGroup,
			ArrayList<Integer> variations, ArrayList<Integer> equipment,
			int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		Statement s = null;
		ResultSet rs = null;
		int exerciseId;

		String query = "insert into exercises (name, musclegroup, creatorid)\r\n"
				+ "values (?, ?, ?)";
		String lastInsert = "select last_insert_id()";
		String query2 = "insert into exercisevariations (exerciseid, variationid)\r\n"
				+ "values (?, ?)";
		String query3 = "insert into exerciseequipment (exerciseid, equipmentid)\r\n"
				+ "values (?,?)";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, name);
			ps.setInt(2, muscleGroup);
			if (userId > 0)
				ps.setInt(3, userId);
			else
				ps.setNull(3, java.sql.Types.INTEGER);

			ps.executeUpdate();

			s = connection.createStatement();
			rs = s.executeQuery(lastInsert);
			rs.first();
			exerciseId = rs.getInt("last_insert_id()");

			for (Integer i : variations) {
				ps = connection.prepareStatement(query2);
				ps.setInt(1, exerciseId);
				ps.setInt(2, i);
				ps.executeUpdate();
			}

			for (Integer i : equipment) {
				ps = connection.prepareStatement(query3);
				ps.setInt(1, exerciseId);
				ps.setInt(2, i);
				ps.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return 0;
	}

	public static int insertVariation(String name, String shortName, int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "insert into variations (variation, shortname, creatorid) "
				+ "values (?,?,?)";

		try {

			ps = connection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, shortName);
			if (userId > 0)
				ps.setInt(3, userId); // isnert under this users id
			else
				ps.setNull(3, java.sql.Types.INTEGER);

			return ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}

	public static ArrayList<Exercise> searchExercises(int accountId,
			int muscleGroup, boolean adminMode) {

		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		ArrayList<Exercise> result = new ArrayList<Exercise>();

		// Selects all exercises owned by user or public
		String query = "select exercises._id, exercises.name, exercises.musclegroup as musclegroupid,\r\n"
				+ "musclegroups.musclegroup, creatorid\r\n"
				+ "from exercises\r\n"
				+ "join musclegroups on (exercises.musclegroup = musclegroups._id)\r\n"
				+ "where creatorid = ? or creatorid is null\r\n"
				//+ "order by musclegroups.musclegroup, exercises.name";
				+ "order by exercises.name";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, accountId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Exercise ex = new Exercise();
				ex.setId(rs.getInt("_id"));
				ex.setName(rs.getString("name"));
				ex.setMuscleGroupId(rs.getInt("musclegroupid"));
				ex.setMuscleGroup(rs.getString("musclegroup"));

				if (rs.getInt("creatorid") == 0)
					ex.setCustom(false);
				else
					ex.setCustom(true);

				ex.setVariations(selectVariations(ex.getId(), accountId));
				ex.setEquipment(selectEquipment(ex.getId(), accountId));
				result.add(ex);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static ArrayList<Equipment> selectAllEquipment(int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Equipment> result = new ArrayList<Equipment>();

		String query = "select _id, equipment, shortname, creatorid from equipment "
				+ "where creatorid = ? or creatorid is null "
				+ "ORDER BY equipment";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Equipment e = new Equipment();
				e.setId(rs.getInt("_id"));
				e.setName(rs.getString("equipment"));
				e.setShortName(rs.getString("shortname"));
				if (rs.getInt("creatorid") == 0)
					e.setCustom(false);
				else
					e.setCustom(true);
				result.add(e);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static ArrayList<Variation> selectAllVariations(int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Variation> result = new ArrayList<Variation>();

		String query = "select _id, variation, shortname,creatorid from variations "
				+ "where creatorid = ? or creatorid is null "
				+ "ORDER BY variation";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Variation variation = new Variation();
				variation.setId(rs.getInt("_id"));
				variation.setName(rs.getString("variation"));
				variation.setShortName(rs.getString("shortname"));
				if (rs.getInt("creatorid") == 0)
					variation.setCustom(false);
				else
					variation.setCustom(true);
				result.add(variation);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static ArrayList<Equipment> selectEquipment(int exerciseId,
			int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Equipment> result = new ArrayList<Equipment>();

		String query = "select equipment._id, equipment, shortname, equipment.creatorid from equipment "
				+ "join exerciseequipment on (equipment._id = exerciseequipment.equipmentid)\r\n"
				+ "join exercises on (exerciseequipment.exerciseid = exercises._id)\r\n"
				+ "where exercises._id = ? "
				+ "AND (equipment.creatorid = ? or equipment.creatorid is null)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, exerciseId);
			ps.setInt(2, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Equipment e = new Equipment();
				e.setId(rs.getInt("_id"));
				e.setName(rs.getString("equipment"));
				e.setShortName(rs.getString("shortname"));
				if (rs.getInt("creatorid") == 0)
					e.setCustom(false);
				else
					e.setCustom(true);
				result.add(e);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static Exercise selectExercise(int exerciseId, int userId) {

		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		Exercise result = new Exercise();

		String query = "select exercises._id, exercises.name, creatorid, exercises.musclegroup as musclegroupid,\r\n"
				+ "musclegroups.musclegroup, creatorid\r\n"
				+ "from exercises\r\n"
				+ "join musclegroups on (exercises.musclegroup = musclegroups._id)\r\n"
				+ "where exercises._id = ? "
				+ "AND (creatorid = ? or creatorid is null)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, exerciseId);
			ps.setInt(2, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				result.setId(rs.getInt("_id"));
				result.setName(rs.getString("name"));
				result.setMuscleGroupId(rs.getInt("musclegroupid"));
				result.setMuscleGroup(rs.getString("musclegroup"));

				if (rs.getInt("creatorid") == 0)
					result.setCustom(false);
				else
					result.setCustom(true);

				result.setVariations(selectVariations(result.getId(), userId));
				result.setEquipment(selectEquipment(result.getId(), userId));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static Map<String, Integer> selectExercises(String muscleGroup) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, Integer> result = new HashMap<String, Integer>();

		String query = "select exercises._id, name from exercises\r\n"
				+ "join musclegroups on (exercises.musclegroup = musclegroups._id)\r\n"
				+ "where musclegroups.musclegroup = ? \r\n" + "order by name";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, muscleGroup);
			rs = ps.executeQuery();

			while (rs.next()) {
				result.put(rs.getString("name"), new Integer(rs.getInt("_id")));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static LinkedHashMap<Integer, String> selectMuscleGroups() {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		Statement s = null;
		ResultSet rs = null;

		String query = "select _id, musclegroup from musclegroups\r\n"
				+ "order by musclegroup";
		LinkedHashMap<Integer, String> result = new LinkedHashMap<Integer, String>();

		try {
			s = connection.createStatement();
			rs = s.executeQuery(query);

			while (rs.next()) {
				result.put(rs.getInt("_id"), rs.getString("musclegroup"));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(s);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static ArrayList<Variation> selectVariations(int exerciseId,
			int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Variation> result = new ArrayList<Variation>();

		String query = "select variations._id, variation, shortname, variations.creatorid from variations "
				+ "join exercisevariations on (variations._id = exercisevariations.variationid)\r\n"
				+ "join exercises on (exercisevariations.exerciseid = exercises._id)\r\n"
				+ "where exercises._id = ? "
				+ "AND (variations.creatorid = ? or variations.creatorid is null)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, exerciseId);
			ps.setInt(2, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Variation variation = new Variation();
				variation.setId(rs.getInt("_id"));
				variation.setName(rs.getString("variation"));
				variation.setShortName(rs.getString("shortname"));
				if (rs.getInt("creatorid") == 0)
					variation.setCustom(false);
				else
					variation.setCustom(true);

				result.add(variation);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	// more parameters to be added
	public static void updateExercise(int exerciseId, String newName,
			Integer newMuscleGroup, ArrayList<Integer> varIds,
			ArrayList<Integer> equipIds) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "update exercises\r\n"
				+ "set name = ?, musclegroup = ?\r\n" + "where _id = ?";
		String deleteVariations = "delete from exercisevariations\r\n"
				+ "where exerciseid = ?";
		String deleteEquipment = "delete from exerciseequipment\r\n"
				+ "where exerciseid = ?";
		String insertVariations = "insert into exercisevariations (exerciseid, variationid)\r\n"
				+ "values (?, ?)";
		String insertEquipment = "insert into exerciseequipment (exerciseid, equipmentid)\r\n"
				+ "values (?, ?)";

		try {
			if (newName != null && newMuscleGroup != null) {
				ps = connection.prepareStatement(query);
				ps.setString(1, newName);
				ps.setInt(2, newMuscleGroup);
				ps.setInt(3, exerciseId);

				ps.executeUpdate();
			}

			ps = connection.prepareStatement(deleteVariations);
			ps.setInt(1, exerciseId);
			ps.executeUpdate();

			ps = connection.prepareStatement(deleteEquipment);
			ps.setInt(1, exerciseId);
			ps.executeUpdate();

			for (Integer i : varIds) {
				ps = connection.prepareStatement(insertVariations);
				ps.setInt(1, exerciseId);
				ps.setInt(2, i);
				ps.executeUpdate();
			}

			for (Integer i : equipIds) {
				ps = connection.prepareStatement(insertEquipment);
				ps.setInt(1, exerciseId);
				ps.setInt(2, i);
				ps.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}
}
